package com.zretc.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.zretc.dao.ProductDao;
import com.zretc.entity.Product;
import com.zretc.entity.Seller;
import com.zretc.entity.Type;
import com.zretc.util.DBUtil;
import com.zretc.util.PageInfo;

/**
 * @author wentao
 * 商品dao层的实现
 */
public class ProductDaoImpl implements ProductDao{

	@Override
	public int insertProduct(Product product) {
		String sql = "insert into product(type_id,seller_id,product_name,product_inventory,product_price,product_picture,product_time,product_status,product_introduce) values(?,?,?,?,?,?,?,?,?)";
		return DBUtil.doUpdate(sql, product.getType().getTypeId(),
									product.getSeller().getSellerId(),
									product.getProductName(),
									product.getProductInventory(),
									product.getProductPrice(),
									product.getProductPicture(),
									product.getProductTime(),
									product.getProductStatus(),
									product.getProductIntroduce());
	}

	@Override
	public PageInfo<Product> findByPage(Map<String, String> params) {
		// 当前页码
		Integer pageNum = Integer.valueOf(params.get("pageNum"));
		// 页面数量
		Integer pageSize = Integer.valueOf(params.get("pageSize"));
		
		StringBuilder sql = new StringBuilder("select ")
				.append(" p.product_id")
				.append(",p.type_id")
				.append(",t.type_name")
				.append(",p.seller_id")
				.append(",s.seller_shop_name")
				.append(",p.product_name")
				.append(",p.product_inventory")
				.append(",p.product_price")
				.append(",p.product_picture")
				.append(",p.product_time")
				.append(",p.product_status")
				.append(",p.product_introduce")
				.append(" from product p")
				.append(" inner join type t")
				.append(" on p.type_id = t.type_id")
				.append(" inner join seller s")
				.append(" on s.seller_id = p.seller_id")
				.append(" where p.product_name like ?")
				.append(" limit ?,?");
		List<Product> data = new ArrayList<Product>();
		ResultSet rs = DBUtil.doQuery(sql.toString(),params.get("productName"),(pageNum-1)*pageSize,pageSize);
		try {
			while(rs.next()) {
				Integer productId = rs.getInt("product_id"); 
				String productName = rs.getString("product_name"); 
				Seller seller = new Seller();
				seller.setSellerId(rs.getInt("seller_id"));
				seller.setSellerShopName(rs.getString("seller_shop_name"));
				Integer productInventory = rs.getInt("product_inventory"); 
				Float productPrice = rs.getFloat("product_price"); 
				String productPicture = rs.getString("product_picture"); 
				Type type = new Type();
				type.setTypeId(rs.getInt("type_id"));
				type.setTypeName(rs.getString("type_name"));
				String productTime = rs.getString("product_time");
				Integer productStatus = rs.getInt("product_status");
				String productIntroduce = rs.getString("product_introduce");
				Product product = new Product(productId, productName, seller, productInventory, productPrice, productPicture, type, productTime, productStatus, productIntroduce);
				data.add(product);
			}
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
		
		// 查询总数量
		Integer total = 0;
		String s = "select count(*) from product where product_name like ?";
		rs = DBUtil.doQuery(s, params.get("productName"));
		try {
			while(rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Product> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}

	@Override
	public int updateProduct(Product product) {
		String sql = "update product set type_id=?,seller_id=?,product_name=?,product_inventory=?,product_price=?,product_picture=?,product_time=?,product_status=?,product_introduce=? where product_id = ?";
		return DBUtil.doUpdate(sql, product.getType().getTypeId(),
				product.getSeller().getSellerId(),
				product.getProductName(),
				product.getProductInventory(),
				product.getProductPrice(),
				product.getProductPicture(),
				product.getProductTime(),
				product.getProductStatus(),
				product.getProductIntroduce(),
				product.getProductId());
	}

	@Override
	public int deleteProduct(int productId) {
		String sql = " delete from product where product_id = ?";
		return DBUtil.doUpdate(sql, productId);
	}

	@Override
	public PageInfo<Product> productListByType(Map<String , String> map) {
		// 当前页码
		Integer pageNum = Integer.valueOf(map.get("pageNum"));
		// 页面数量
		Integer pageSize = Integer.valueOf(map.get("pageSize")); 
		// 分类编号
		Integer typeId = Integer.valueOf(map.get("typeId"));
		
		StringBuilder sql = new StringBuilder("select ")
				.append(" p.product_id")
				.append(",p.type_id")
				.append(",t.type_name")
				.append(",p.seller_id")
				.append(",s.seller_shop_name")
				.append(",p.product_name")
				.append(",p.product_inventory")
				.append(",p.product_price")
				.append(",p.product_picture")
				.append(",p.product_time")
				.append(",p.product_status")
				.append(",p.product_introduce")
				.append(" from product p")
				.append(" inner join type t")
				.append(" on p.type_id = t.type_id")
				.append(" inner join seller s")
				.append(" on s.seller_id = p.seller_id")
				.append(" where p.type_id = ?")
				.append(" and p.product_status = 1")
				.append(" limit ?,?");
		List<Product> data = new ArrayList<Product>();
		ResultSet rs = DBUtil.doQuery(sql.toString(),typeId,(pageNum-1)*pageSize,pageSize);
		try {
			while(rs.next()) {
				Integer productId = rs.getInt("product_id"); 
				String productName = rs.getString("product_name"); 
				Seller seller = new Seller();
				seller.setSellerId(rs.getInt("seller_id"));
				seller.setSellerShopName(rs.getString("seller_shop_name"));
				Integer productInventory = rs.getInt("product_inventory"); 
				Float productPrice = rs.getFloat("product_price"); 
				String productPicture = rs.getString("product_picture"); 
				Type type = new Type();
				type.setTypeId(rs.getInt("type_id"));
				type.setTypeName(rs.getString("type_name"));
				String productTime = rs.getString("product_time");
				Integer productStatus = rs.getInt("product_status");
				String productIntroduce = rs.getString("product_introduce");
				Product product = new Product(productId, productName, seller, productInventory, productPrice, productPicture, type, productTime, productStatus, productIntroduce);
				data.add(product);
			}
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
		
		// 查询总数量
		Integer total = 0;
		String s = "select count(*) from product where type_id = ? and product_status = 1";
		rs = DBUtil.doQuery(s, typeId);
		try {
			while(rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Product> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}

	@Override
	public PageInfo<Product> productListBySeller(Map<String, String> map) {
		// 当前页码
		Integer pageNum = Integer.valueOf(map.get("pageNum"));
		// 页面数量
		Integer pageSize = Integer.valueOf(map.get("pageSize")); 
		// 分类编号
		Integer sellerId = Integer.valueOf(map.get("sellerId"));
		// 获取关键字
		String keyword = map.get("keyword");
		if(keyword != null) {
			keyword = "%"+keyword+"%";
		}else {
			keyword = "%";
		}
		
		StringBuilder sql = new StringBuilder("select")
				.append(" p.product_id")
				.append(",p.type_id")
				.append(",t.type_name")
				.append(",p.seller_id")
				.append(",s.seller_shop_name")
				.append(",p.product_name")
				.append(",p.product_inventory")
				.append(",p.product_price")
				.append(",p.product_picture")
				.append(",p.product_time")
				.append(",p.product_status")
				.append(",p.product_introduce")
				.append(" from product p")
				.append(" inner join type t")
				.append(" on p.type_id = t.type_id")
				.append(" inner join seller s ")
				.append(" on s.seller_id = p.seller_id")
				.append(" where p.seller_id = ?")
				.append(" and p.product_status = 1")
				.append(" and p.product_name like ?")
				.append(" limit ?,?");
		List<Product> data = new ArrayList<Product>();
		ResultSet rs = DBUtil.doQuery(sql.toString(),sellerId,keyword,(pageNum-1)*pageSize,pageSize);
		try {
			while(rs.next()) {
				Integer productId = rs.getInt("product_id"); 
				String productName = rs.getString("product_name"); 
				Seller seller = new Seller();
				seller.setSellerId(rs.getInt("seller_id"));
				seller.setSellerShopName(rs.getString("seller_shop_name"));
				Integer productInventory = rs.getInt("product_inventory"); 
				Float productPrice = rs.getFloat("product_price"); 
				String productPicture = rs.getString("product_picture"); 
				Type type = new Type();
				type.setTypeId(rs.getInt("type_id"));
				type.setTypeName(rs.getString("type_name"));
				String productTime = rs.getString("product_time");
				Integer productStatus = rs.getInt("product_status");
				String productIntroduce = rs.getString("product_introduce");
				Product product = new Product(productId, productName, seller, productInventory, productPrice, productPicture, type, productTime, productStatus, productIntroduce);
				data.add(product);
			}
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
		
		// 查询总数量
		Integer total = 0;
		String s = "select count(*) from product where seller_id = ? and product_status = 1";
		rs = DBUtil.doQuery(s, sellerId);
		try {
			while(rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Product> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}

	@Override
	public Product getProductByProductId(Integer productId) {
		StringBuilder sql = new StringBuilder("select ")
										.append(" p.product_id")
										.append(",p.type_id")
										.append(",t.type_name")
										.append(",p.seller_id")
										.append(",s.seller_shop_name")
										.append(",p.product_name")
										.append(",p.product_inventory")
										.append(",p.product_price")
										.append(",p.product_picture")
										.append(",p.product_time")
										.append(",p.product_status")
										.append(",p.product_introduce")
										.append(" from product p")
										.append(" inner join type t")
										.append(" on p.type_id = t.type_id")
										.append(" inner join seller s")
										.append(" on s.seller_id = p.seller_id")
										.append(" where p.product_id = ?");
		Product product = null;
		ResultSet rs = DBUtil.doQuery(sql.toString(), productId);
		try {
			while(rs.next()) {
				productId = rs.getInt("product_id"); 
				String productName = rs.getString("product_name"); 
				Seller seller = new Seller();
				seller.setSellerId(rs.getInt("seller_id"));
				seller.setSellerShopName(rs.getString("seller_shop_name"));
				Integer productInventory = rs.getInt("product_inventory"); 
				Float productPrice = rs.getFloat("product_price"); 
				String productPicture = rs.getString("product_picture"); 
				Type type = new Type();
				type.setTypeId(rs.getInt("type_id"));
				type.setTypeName(rs.getString("type_name"));
				String productTime = rs.getString("product_time");
				Integer productStatus = rs.getInt("product_status");
				String productIntroduce = rs.getString("product_introduce");
				product = new Product(productId, productName, seller, productInventory, productPrice, productPicture, type, productTime, productStatus, productIntroduce);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return product;
	}

	@Override
	public PageInfo<Product> productListByStatus(Map<String, String> map) {
		// 当前页码
		Integer pageNum = Integer.valueOf(map.get("pageNum"));
		// 页面数量
		Integer pageSize = Integer.valueOf(map.get("pageSize")); 
		
		StringBuilder sql = new StringBuilder("select ")
				.append(" p.product_id")
				.append(",p.type_id")
				.append(",t.type_name")
				.append(",p.seller_id")
				.append(",s.seller_shop_name")
				.append(",p.product_name")
				.append(",p.product_inventory")
				.append(",p.product_price")
				.append(",p.product_picture")
				.append(",p.product_time")
				.append(",p.product_status")
				.append(",p.product_introduce")
				.append(" from product p")
				.append(" inner join type t")
				.append(" on p.type_id = t.type_id")
				.append(" inner join seller s")
				.append(" on s.seller_id = p.seller_id")
				.append(" where p.product_status = 1")
				.append(" limit ?,?");
		List<Product> data = new ArrayList<Product>();
		ResultSet rs = DBUtil.doQuery(sql.toString(),(pageNum-1)*pageSize,pageSize);
		try {
			while(rs.next()) {
				Integer productId = rs.getInt("product_id"); 
				String productName = rs.getString("product_name"); 
				Seller seller = new Seller();
				seller.setSellerId(rs.getInt("seller_id"));
				seller.setSellerShopName(rs.getString("seller_shop_name"));
				Integer productInventory = rs.getInt("product_inventory"); 
				Float productPrice = rs.getFloat("product_price"); 
				String productPicture = rs.getString("product_picture"); 
				Type type = new Type();
				type.setTypeId(rs.getInt("type_id"));
				type.setTypeName(rs.getString("type_name"));
				String productTime = rs.getString("product_time");
				Integer productStatus = rs.getInt("product_status");
				String productIntroduce = rs.getString("product_introduce");
				Product product = new Product(productId, productName, seller, productInventory, productPrice, productPicture, type, productTime, productStatus, productIntroduce);
				data.add(product);
			}
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
		
		// 查询总数量
		Integer total = 0;
		String s = "select count(*) from product where product_status = 1";
		rs = DBUtil.doQuery(s);
		try {
			while(rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Product> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}

	@Override
	public PageInfo<Product> productListByFatherType(Map<String, String> map) {
		// 当前页码
		Integer pageNum = Integer.valueOf(map.get("pageNum"));
		// 页面数量
		Integer pageSize = Integer.valueOf(map.get("pageSize")); 
		// 分类编号
		Integer typeFatherId = Integer.valueOf(map.get("typeFatherId"));
		
		StringBuilder sql = new StringBuilder("select ")
				.append(" p.product_id")
				.append(",p.type_id")
				.append(",t.type_name")
				.append(",p.seller_id")
				.append(",s.seller_shop_name")
				.append(",p.product_name")
				.append(",p.product_inventory")
				.append(",p.product_price")
				.append(",p.product_picture")
				.append(",p.product_time")
				.append(",p.product_status")
				.append(",p.product_introduce")
				.append(" from product p")
				.append(" inner join type t")
				.append(" on p.type_id = t.type_id")
				.append(" inner join seller s")
				.append(" on s.seller_id = p.seller_id")
				.append(" where t.type_father_id = ?")
				.append(" and p.product_status = 1")
				.append(" limit ?,?");
		List<Product> data = new ArrayList<Product>();
		ResultSet rs = DBUtil.doQuery(sql.toString(),typeFatherId,(pageNum-1)*pageSize,pageSize);
		try {
			while(rs.next()) {
				Integer productId = rs.getInt("product_id"); 
				String productName = rs.getString("product_name"); 
				Seller seller = new Seller();
				seller.setSellerId(rs.getInt("seller_id"));
				seller.setSellerShopName(rs.getString("seller_shop_name"));
				Integer productInventory = rs.getInt("product_inventory"); 
				Float productPrice = rs.getFloat("product_price"); 
				String productPicture = rs.getString("product_picture"); 
				Type type = new Type();
				type.setTypeId(rs.getInt("type_id"));
				type.setTypeName(rs.getString("type_name"));
				String productTime = rs.getString("product_time");
				Integer productStatus = rs.getInt("product_status");
				String productIntroduce = rs.getString("product_introduce");
				Product product = new Product(productId, productName, seller, productInventory, productPrice, productPicture, type, productTime, productStatus, productIntroduce);
				data.add(product);
			}
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
		
		// 查询总数量
		Integer total = 0;
		String s = "select count(*) from product where type_id in (select type_id from type where type_father_id = ?) and product_status = 1";
		rs = DBUtil.doQuery(s, typeFatherId);
		try {
			while(rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageInfo<Product> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}

	@Override
	public PageInfo<Map<String, Object>> productListByMoreCondition(Map<String, String> map) {
		
		// 获取页码
		Integer pageNum = Integer.valueOf(map.get("pageNum"));
		// 获取显示数量
		Integer pageSize = Integer.valueOf(map.get("pageSize"));
		// 计算偏移量
		Integer offset = (pageNum-1)*pageSize;
		// 获取排序字段
		String orderBy = map.get("orderBy");
		// 获取排序顺序
		String ascDesc = map.get("ascDesc");
		// 获取关键字
		String keyword = map.get("keyword");
		
		// 创建SQL语句
		StringBuilder sql = new StringBuilder("select ")
				.append(" p.product_id")
				.append(",t.type_name")
				.append(",ty.type_name as type_father_name")
				.append(",s.seller_shop_name")
				.append(",p.product_name")
				.append(",p.product_price")
				.append(",p.product_time")
				.append(",p.product_introduce")
				.append(" from product p")
				.append(" inner join type t")
				.append(" on p.type_id = t.type_id")
				.append(" inner join type ty")
				.append(" on ty.type_id = t.type_father_id")
				.append(" inner join seller s")
				.append(" on s.seller_id = p.seller_id")
				.append(" where p.product_status = 1")
				.append(" and (p.product_name like ?")
				.append(" or t.type_name like ?")
				.append(" or ty.type_name like ?)");
		if("productName".equals(orderBy)) {
			if("asc".equals(ascDesc)) {
				sql.append(" order by p.product_name asc");
			}else {
				sql.append(" order by p.product_name desc");
			}
		}else if("productPrice".equals(orderBy)) {
			if("asc".equals(ascDesc)) {
				sql.append(" order by p.product_price asc");
			}else {
				sql.append(" order by p.product_price desc");
			}
		}else if("productTime".equals(orderBy)) {
			if("asc".equals(ascDesc)) {
				sql.append(" order by p.product_time asc");
			}else {
				sql.append(" order by p.product_time desc");
			}
		}else if("typeId".equals(orderBy)) {
			if("asc".equals(ascDesc)) {
				sql.append(" order by p.type_id asc");
			}else {
				sql.append(" order by p.type_id desc");
			}
		}else if("typeFatherId".equals(orderBy)) {
			if("asc".equals(ascDesc)) {
				sql.append(" order by t.type_father_id asc");
			}else {
				sql.append(" order by t.type_father_id desc");
			}
		}else if("sellerShopName".equals(orderBy)) {
			if("asc".equals(ascDesc)) {
				sql.append(" order by s.seller_shop_name asc");
			}else {
				sql.append(" order by s.seller_shop_name desc");
			}
		}
		sql.append(" limit ?,?");
		List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();
		ResultSet rs = DBUtil.doQuery(sql.toString(),keyword,keyword,keyword,offset,pageSize);
		try {
			while(rs.next()) {
				Integer productId = rs.getInt("product_id"); 
				String productName = rs.getString("product_name");
				String typeName = rs.getString("type_name");
				String typeFatherName = rs.getString("type_father_name");
				String sellerShopName = rs.getString("seller_shop_name");
				Float productPrice = rs.getFloat("product_price");
				String productTime = rs.getString("product_time");
				String productIntroduce = rs.getString("product_introduce");
				Map<String,Object> m = new HashMap<String, Object>();
				m.put("productId", productId);
				m.put("productName", productName);
				m.put("typeName", typeName);
				m.put("typeFatherName", typeFatherName);
				m.put("sellerShopName", sellerShopName);
				m.put("productPrice", productPrice);
				m.put("productTime", productTime);
				m.put("productIntroduce", productIntroduce);
				data.add(m);
			}
		} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		}
		
		// 查询总数量
		Integer total = 0;
		StringBuilder s = new StringBuilder("select count(*)")
				.append(" from product p")
				.append(" inner join type t")
				.append(" on p.type_id = t.type_id")
				.append(" inner join type ty")
				.append(" on ty.type_id = t.type_father_id")
				.append(" inner join seller s")
				.append(" on s.seller_id = p.seller_id")
				.append(" where p.product_status = 1")
				.append(" and (p.product_name like ?")
				.append(" or t.type_name like ?")
				.append(" or ty.type_name like ?)");
		rs = DBUtil.doQuery(s.toString(), keyword,keyword,keyword);
		try {
			while(rs.next()) {
				total = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		PageInfo<Map<String, Object>> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
		return pageInfo;
	}

	@Override
	public PageInfo<Product> productListBySellerIdandKeyword(Map<String, String> map) {
		// 当前页码
				Integer pageNum = Integer.valueOf(map.get("pageNum"));
				// 页面数量
				Integer pageSize = Integer.valueOf(map.get("pageSize"));
				// 获取关键字
				String keyword = map.get("keyword");
				StringBuilder sql = new StringBuilder("select ")
						.append(" p.product_id")
						.append(",p.type_id")
						.append(",t.type_name")
						.append(",p.seller_id")
						.append(",s.seller_shop_name")
						.append(",p.product_name")
						.append(",p.product_inventory")
						.append(",p.product_price")
						.append(",p.product_picture")
						.append(",p.product_time")
						.append(",p.product_status")
						.append(",p.product_introduce")
						.append(" from product p")
						.append(" inner join type t")
						.append(" on p.type_id = t.type_id")
						.append(" inner join seller s")
						.append(" on s.seller_id = p.seller_id")
						.append(" where s.seller_id = ? and p.product_name like ?")
						.append(" limit ?,?");
				List<Product> data = new ArrayList<Product>();
				ResultSet rs = DBUtil.doQuery(sql.toString(),map.get("sellerId"),keyword,(pageNum-1)*pageSize,pageSize);
				try {
					while(rs.next()) {
						Integer productId = rs.getInt("product_id"); 
						String productName = rs.getString("product_name"); 
						Seller seller = new Seller();
						seller.setSellerId(rs.getInt("seller_id"));
						seller.setSellerShopName(rs.getString("seller_shop_name"));
						Integer productInventory = rs.getInt("product_inventory"); 
						Float productPrice = rs.getFloat("product_price"); 
						String productPicture = rs.getString("product_picture"); 
						Type type = new Type();
						type.setTypeId(rs.getInt("type_id"));
						type.setTypeName(rs.getString("type_name"));
						String productTime = rs.getString("product_time");
						Integer productStatus = rs.getInt("product_status");
						String productIntroduce = rs.getString("product_introduce");
						Product product = new Product(productId, productName, seller, productInventory, productPrice, productPicture, type, productTime, productStatus, productIntroduce);
						data.add(product);
					}
				} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				}
				
				// 查询总数量
				Integer total = 0;
				String s = "select count(*) from product where seller_id = ? and product_name like ?";
				rs = DBUtil.doQuery(s.toString(), map.get("sellerId"),keyword);
				try {
					while(rs.next()) {
						total = rs.getInt(1);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				PageInfo<Product> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
				return pageInfo;
}

	@Override
	public PageInfo<Product> productListBySellerIdandKeywordandStatus(Map<String, String> map) {
		        // 当前页码
				Integer pageNum = Integer.valueOf(map.get("pageNum"));
				// 页面数量
				Integer pageSize = Integer.valueOf(map.get("pageSize")); 
				// 分类编号
				Integer sellerId = Integer.valueOf(map.get("sellerId"));
				// 关键字
				String keyword = map.get("keyword");
				
				StringBuilder sql = new StringBuilder("select")
						.append(" p.product_id")
						.append(",p.type_id")
						.append(",t.type_name")
						.append(",p.seller_id")
						.append(",s.seller_shop_name")
						.append(",p.product_name")
						.append(",p.product_inventory")
						.append(",p.product_price")
						.append(",p.product_picture")
						.append(",p.product_time")
						.append(",p.product_status")
						.append(",p.product_introduce")
						.append(" from product p")
						.append(" inner join type t")
						.append(" on p.type_id = t.type_id")
						.append(" inner join seller s ")
						.append(" on s.seller_id = p.seller_id")
						.append(" where p.seller_id = ?")
						.append(" and p.product_status = 1")
						.append(" and p.product_name like ? ")
						.append(" limit ?,?");
				List<Product> data = new ArrayList<Product>();
				ResultSet rs = DBUtil.doQuery(sql.toString(),sellerId,keyword,(pageNum-1)*pageSize,pageSize);
				try {
					while(rs.next()) {
						Integer productId = rs.getInt("product_id"); 
						String productName = rs.getString("product_name"); 
						Seller seller = new Seller();
						seller.setSellerId(rs.getInt("seller_id"));
						seller.setSellerShopName(rs.getString("seller_shop_name"));
						Integer productInventory = rs.getInt("product_inventory"); 
						Float productPrice = rs.getFloat("product_price"); 
						String productPicture = rs.getString("product_picture"); 
						Type type = new Type();
						type.setTypeId(rs.getInt("type_id"));
						type.setTypeName(rs.getString("type_name"));
						String productTime = rs.getString("product_time");
						Integer productStatus = rs.getInt("product_status");
						String productIntroduce = rs.getString("product_introduce");
						Product product = new Product(productId, productName, seller, productInventory, productPrice, productPicture, type, productTime, productStatus, productIntroduce);
						data.add(product);
					}
				} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				}
				
				// 查询总数量
				Integer total = 0;
				String s = "select count(*) from product where seller_id = ? and product_status = 1 and product_name like ?";
				rs = DBUtil.doQuery(s, sellerId,keyword);
				try {
					while(rs.next()) {
						total = rs.getInt(1);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				PageInfo<Product> pageInfo = new PageInfo<>(data, pageNum, pageSize, total);
				return pageInfo;
			}

	@Override
	public List<Map<String, Object>> productListSalesTop() {
		String sql = "select p.product_id,p.product_picture,p.product_name,p.product_price,sum(s.sales_count) as count from product p left join sales s on s.product_id = p.product_id group by product_id order by sum(s.sales_count) desc limit 0,4";
		ResultSet rs = DBUtil.doQuery(sql);
		List<Map<String, Object>> list = new ArrayList<>();
		try {
			while(rs.next()) {
				int productId = rs.getInt("product_id");
				String productPicture = rs.getString("product_picture");
				String productName = rs.getString("product_name");
				float productPrice = rs.getFloat("product_price");
				int count = rs.getInt("count");
				Map<String, Object> map = new HashMap<>();
				map.put("productId", productId);
				map.put("productPicture", productPicture);
				map.put("productName", productName);
				map.put("productPrice", productPrice);
				map.put("count", count);
				list.add(map);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
	}

	@Override
	public List<Product> productListNewTop() {
		String sql = "select product_id,product_picture,product_name,product_price,product_time from product order by product_time DESC limit 0,4;";
		ResultSet rs = DBUtil.doQuery(sql);
		List<Product> list = new ArrayList<>();
		try {
			while(rs.next()) {
				int productId = rs.getInt("product_id");
				String productPicture = rs.getString("product_picture");
				String productName = rs.getString("product_name");
				float productPrice = rs.getFloat("product_price");
				String productTime = rs.getString("product_time");
				Product product = new Product(productId, productName, null, null, productPrice, productPicture, null, productTime, null, null);
				list.add(product);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Product findProductInventoryByProductId(Integer productId) {
		Product product = null;
		ResultSet rs = null;
		StringBuilder sb = new StringBuilder();
		sb.append("select")
		.append(" product_inventory")
		.append(" from product")
		.append(" where product_id = ?");
		rs = DBUtil.doQuery(sb.toString(), productId);
		try {
			while (rs.next()) {
				Integer productInventory = rs.getInt("product_inventory");
				product = new Product();
				product.setProductInventory(productInventory);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return product;
	}

	@Override
	public int updateProductInventoryByProductId(Product product) {
		String sql = "update product set product_inventory = ? where product_id = ?";
		return DBUtil.doUpdate(sql, product.getProductInventory(), product.getProductId());
	}
}
